package sk.stuba.fiit.foo07.genex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import sk.stuba.fiit.foo07.genex.beans.Answer;
import sk.stuba.fiit.foo07.genex.beans.Category;
import sk.stuba.fiit.foo07.genex.beans.Picture;
import sk.stuba.fiit.foo07.genex.beans.Question;
import sk.stuba.fiit.foo07.genex.exceptions.QuestionInTestException;

/**
 * @author palo
 * 
 * Zname chyby: Delete vyhodi exception pri odstranovani otazky ked existuje
 * zaznam medzi otazkou a testom (neviem ci to mam riesit). Podobnu chybu medzi
 * kategoriou a otazkou riesim, cize aby to bolo "konzistnetne" mali by sme sa
 * dohodnut
 * 
 */
public class QuestionDaoDerby extends BasicDao implements QuestionDao {

    private static final String sqlAddQuestion = "INSERT INTO Question VALUES(?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,DEFAULT, ?, ?, ?)";
    private static final String sqlAddCategoryQuestion = "INSERT INTO CategoryQuestion VALUES (?, ?)";
    private static final String sqlDeleteCategoryQuestion = "DELETE FROM CategoryQuestion WHERE questionID = ?";
    private static final String sqlDeleteQuestionByID = "DELETE FROM Question WHERE questionID = ?";
    private static final String sqlGetQuestionByID = "SELECT * FROM Question WHERE questionID = ?";
    private static final String sqlGetQuestionsByCategoryID = "SELECT * FROM Question, CategoryQuestion WHERE Question.questionID = CategoryQuestion.questionID AND CategoryQuestion.categoryID =?";
    private static final String sqlGetQuestionsInRootCategory = "SELECT * FROM Question, CategoryQuestion WHERE Question.questionID = CategoryQuestion.questionID AND CategoryQuestion.categoryID IS NULL";
    private static final String sqlGetQuestionByTestID = "SELECT * FROM Question, TestQuestion WHERE TestQuestion.QuestionID = Question.questionID AND TestQuestion.testID = ?";
    private static final String sqlUpdateQuestion = "UPDATE Question SET Text = ?, Difficulty = ?, LastUpdate=CURRENT_TIMESTAMP, userID = ?, questionTypeID = ?, derivedFromID = ? WHERE questionID = ?";
    private static final String sqlMoveQuestion = "UPDATE TestQuestion SET testID = ? WHERE testID = ? and questionID = ?";
    private static final String sqlIsQuestionInTest = "SELECT * FROM TestQuestion WHERE questionID = ?";
    private static final String sqlGetQuestionCount = "SELECT COUNT(*) FROM CategoryQuestion WHERE CategoryQuestion.categoryID = ?";
    private static final String sqlDeleteQuestionFromCategory = "DELETE FROM CategoryQuestion WHERE questionID = ? AND categoryID = ?";

    protected ResultSet rs;
    protected PreparedStatement st;
    protected Question q;
    protected ArrayList<Question> q_list;
    protected ArrayList<Integer> idList;

    public QuestionDaoDerby(Connection con) {
        super(con);
    }

    public QuestionDaoDerby() throws Exception {
        super();
    }

    @Override
    public void createQuestion(Question toCreate) throws SQLException {
        try {
            st = con.prepareStatement(sqlAddQuestion,
                    Statement.RETURN_GENERATED_KEYS);

            st.setString(1, toCreate.getText());
            st.setInt(2, toCreate.getDifficulty());
            st.setInt(3, toCreate.getUserID());
            st.setInt(4, toCreate.getQuestionTypeID());
            Integer derivedFrom = toCreate.getDerivedFromID();
            if (derivedFrom == null) {
                st.setNull(5, java.sql.Types.INTEGER);
            } else
                st.setInt(5, derivedFrom);

            st.execute();

            ResultSet rs = st.getGeneratedKeys();
            rs.next();

            toCreate.setQuestionID(rs.getInt(1));
        } catch (SQLException ex) {
            st = null;
            throw ex;
        }
    }

    @Override
    public void addQuestion(Integer categoryID, Question toAdd)
            throws SQLException {
        try {
            createQuestion(toAdd);

            st = con.prepareStatement(sqlAddCategoryQuestion);
            if (categoryID == null) {
                st.setNull(1, java.sql.Types.INTEGER);
            } else {
                st.setInt(1, categoryID);
            }

            st.setInt(2, toAdd.getQuestionID());

            st.executeUpdate();

        } catch (SQLException e) {
            st = null;
            throw e;
        }
    }

    @Override
    public void addQuestions(Integer categoryID, ArrayList<Question> toAdd)
            throws SQLException {

        for (Question q : toAdd) {
            addQuestion(categoryID, q);
        }
    }

    private boolean isQuestionInTest(int questionID) throws SQLException {
        st = con.prepareStatement(sqlIsQuestionInTest);
        st.setInt(1, questionID);
        rs = st.executeQuery();

        if (rs.next()) {
            return true;
        }
        return false;
    }

    @Override
    public void deleteQuestion(Question toDelete)
            throws QuestionInTestException, SQLException {
        try {

            if (isQuestionInTest(toDelete.getQuestionID())) {
                throw new QuestionInTestException(
                        "Unable to delete: question is in test");
            }
            con.setAutoCommit(false);
            con.setSavepoint();

            st = con.prepareStatement(sqlDeleteCategoryQuestion);
            st.setInt(1, toDelete.getQuestionID());

            st.executeUpdate();

            AnswerDao aDao = new AnswerDaoDerby(con);
            ArrayList<Answer> ans = aDao.getAnswersByQuestionID(toDelete
                    .getQuestionID());
            for (Answer a : ans) {
                aDao.deleteAnswer(a);
            }

            PictureDao picDao = new PictureDaoDerby(con);
            for (Picture p : picDao.getPicturesByQuestionID(toDelete
                    .getQuestionID())) {
                picDao.deletePictureFromQuestion(p.getPictureID(), toDelete
                        .getQuestionID());

            }

            KeywordDao kDao = new KeywordDaoDerby(con);
            kDao.deleteAllKeywordsFromQuestion(toDelete.getQuestionID());

            st = con.prepareStatement(sqlDeleteQuestionByID);
            st.setInt(1, toDelete.getQuestionID());

            // this can fail if question is in any test
            st.executeUpdate();
            con.setAutoCommit(true);
        } catch (SQLException e) {
            con.rollback();
            con.setAutoCommit(true);
            st = null;
            if ("23503".equals(e.getSQLState()))
                throw new QuestionInTestException(e);
            else
                throw e;

        }

    }

    @Override
    public void deleteQuestions(ArrayList<Question> toDelete)
            throws QuestionInTestException, SQLException {

        for (Question q : toDelete) {
            deleteQuestion(q);
        }

    }

    @Override
    public Question getQuestionByID(Integer questionID) throws SQLException {
        try {

            st = con.prepareStatement(sqlGetQuestionByID);

            st.setInt(1, questionID);
            rs = st.executeQuery();

            rs.next();
            q = new Question();
            resultSetToQuestion();
            rs.close();

        } catch (SQLException sqle) {

            q = null;
            st = null;
            rs = null;
            throw sqle;
        }

        return q;
    }

    @Override
    public ArrayList<Question> getQuestionsByIDs(ArrayList<Integer> questionIDs)
            throws SQLException {

        try {
            q_list = new ArrayList<Question>(questionIDs.size());

            for (Integer i : questionIDs) {
                q_list.add(getQuestionByID(i));
            }
        } catch (SQLException sqle) {
            q = null;
            st = null;
            rs = null;
            q_list = null;
            throw sqle;
        }

        return q_list;
    }

    /**
     * helper method
     * 
     * @throws SQLException
     */
    private void resultSetToQuestion() throws SQLException {
        q.setText(rs.getString("Text"));
        q.setDifficulty(rs.getInt("Difficulty"));
        q.setCreated(rs.getTimestamp("Created"));
        q.setLastUpdate(rs.getTimestamp("LastUpdate"));
        q.setQuestionID(rs.getInt("questionID"));
        q.setUserID(rs.getInt("userID"));
        q.setQuestionTypeID(rs.getInt("questionTypeID"));
        Integer i = rs.getInt("derivedFromID");
        if (rs.wasNull())
            q.setDerivedFromID(null);
        else
            q.setDerivedFromID(i);
    }

    @Override
    public ArrayList<Question> getQuestionsByCategoryID(Integer categoryID)
            throws SQLException {
        try {
            if (categoryID == null) {
                st = con.prepareStatement(sqlGetQuestionsInRootCategory,
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
            } else {
                st = con.prepareStatement(sqlGetQuestionsByCategoryID,
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);

                st.setInt(1, categoryID);
            }

            rs = st.executeQuery();

            rs.last();
            q_list = new ArrayList<Question>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                q = new Question();
                resultSetToQuestion();

                q_list.add(q);
            }
            rs.close();

        } catch (SQLException e) {
            q = null;
            st = null;
            rs = null;
            throw e;
        }
        return q_list;
    }

    @Override
    public ArrayList<Question> getQuestionsByTestID(Integer testID)
            throws SQLException {
        try {

            st = con.prepareStatement(sqlGetQuestionByTestID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            st.setInt(1, testID);

            rs = st.executeQuery();

            rs.last();
            q_list = new ArrayList<Question>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                q = new Question();
                resultSetToQuestion();

                q_list.add(q);
            }
            rs.close();

        } catch (SQLException e) {
            q = null;
            st = null;
            rs = null;
            throw e;
        }
        return q_list;

    }

    @Override
    public void updateQuestion(Integer questionID, Question toUpdate)
            throws SQLException {
        try {
            st = con.prepareStatement(sqlUpdateQuestion);

            st.setString(1, toUpdate.getText());
            st.setInt(2, toUpdate.getDifficulty());
            st.setInt(3, toUpdate.getUserID());
            st.setInt(4, toUpdate.getQuestionTypeID());
            if (toUpdate.getDerivedFromID() == null) {
                st.setNull(5, java.sql.Types.INTEGER);
            } else
                st.setInt(5, toUpdate.getDerivedFromID());
            st.setInt(6, toUpdate.getQuestionID());

            st.executeUpdate();

        } catch (SQLException e) {
            st = null;
            throw e;
        }
    }

    /**
     * helper method which builds string representation of query for selecting
     * questions acceptable according to input parameters
     * 
     * @return string representation of query
     * @throws SQLException
     */
    private String buildQuery(int categoryIdCount, int keywordIdCount,
            int questionTypeCount, boolean containsPictures, Integer difficulty)
            throws SQLException {

        int i = 0;

        StringBuilder query = new StringBuilder(
                "SELECT DISTINCT Question.questionID AS questionID ");

        StringBuilder buildFromClause = new StringBuilder(
                " FROM Question, CategoryQuestion ");
        StringBuilder buildWhereClause = new StringBuilder(
                " WHERE Question.questionID = CategoryQuestion.questionID AND ( ");

        for (i = 1; i < categoryIdCount; i++) {
            buildWhereClause.append(" CategoryQuestion.CategoryID = ? OR ");
        }
        buildWhereClause.append(" CategoryQuestion.CategoryID = ? ) ");

        if (keywordIdCount != 0) {
            buildFromClause.append(" , KeywordQuestion ");
            buildWhereClause
                    .append(" AND Question.QuestionID = KeywordQuestion.QuestionID AND ( ");
            for (i = 1; i < keywordIdCount; i++) {
                buildWhereClause.append(" KeywordQuestion.KeywordID = ? OR ");
            }
            buildWhereClause.append(" KeywordQuestion.KeywordID = ? ) ");
        }

        if (questionTypeCount != 0) {
            buildWhereClause.append(" AND ( ");
            for (i = 1; i < questionTypeCount; i++) {
                buildWhereClause.append(" Question.QuestionTypeID = ? OR ");
            }
            buildWhereClause.append(" Question.QuestionTypeID = ? ) ");
        }

        if (!containsPictures) {
            buildFromClause.append(" , PictureQuestion ");
            buildWhereClause
                    .append(" AND ( Question.QuestionID NOT IN ( SELECT DISTINCT PictureQuestion.QuestionID FROM PictureQuestion ) ) ");
        }

        if (difficulty != null) {
            buildWhereClause
                    .append("AND (Question.Difficulty = ?) ORDER BY CATEGORYQUESTION.CATEGORYID");
        }

        query.append(buildFromClause);
        query.append(buildWhereClause);
        System.out.println(query.toString());
        return query.toString();
    }

    /**
     * helper method which supply built query with input parameters
     * 
     * @return prepared statement supplied with parameters
     * @throws SQLException
     */
    private PreparedStatement prepareQuery(String query,
            ArrayList<Integer> questionCategoryIDs,
            ArrayList<Integer> keywordIDs, ArrayList<Integer> questionTypeIDs,
            boolean containsPictures, Integer difficulty) throws SQLException {
        PreparedStatement prep;
        int parameterCounter = 1;

        prep = con.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        for (Integer i : questionCategoryIDs) {
            prep.setInt(parameterCounter, i);
            parameterCounter++;
        }

        for (Integer i : keywordIDs) {
            prep.setInt(parameterCounter, i);
            parameterCounter++;
        }

        for (Integer i : questionTypeIDs) {
            prep.setInt(parameterCounter, i);
            parameterCounter++;
        }

        if (difficulty != null) {
            prep.setInt(parameterCounter, difficulty);
        }

        return prep;
    }

    @Override
    public ArrayList<Integer> getQuestionsForTestGenerator(
            ArrayList<Integer> questionCategoryIDs,
            ArrayList<Integer> keywordIDs, ArrayList<Integer> questionTypeIDs,
            boolean containsPictures, Integer difficulty) throws SQLException {

        try {
            String query = buildQuery(questionCategoryIDs.size(), keywordIDs
                    .size(), questionTypeIDs.size(), containsPictures,
                    difficulty);
            st = prepareQuery(query, questionCategoryIDs, keywordIDs,
                    questionTypeIDs, containsPictures, difficulty);

            rs = st.executeQuery();

            rs.last();
            idList = new ArrayList<Integer>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                idList.add(rs.getInt("QuestionId"));
            }

            rs.close();

            return idList;

        } catch (SQLException e) {
            q = null;
            st = null;
            rs = null;
            q_list = null;
            throw e;
        }

    }

    @Override
    public void moveQuestion(Question q, Integer fromCategoryID,
            Integer toCategoryID) throws SQLException {
        try {
            st = con.prepareStatement(sqlMoveQuestion);
            st.setInt(1, q.getQuestionID());
            st.setInt(2, fromCategoryID);
            st.setInt(3, toCategoryID);

            st.executeUpdate();
        } catch (SQLException e) {
            q = null;
            st = null;
            rs = null;
            throw e;
        }

    }

    @Override
    public void copyQuestion(int questionID, Integer toCategoryID)
            throws SQLException {
        try {
            st = con.prepareStatement(sqlAddCategoryQuestion);

            if (toCategoryID == null) {
                st.setNull(1, java.sql.Types.INTEGER);
            } else {
                st.setInt(1, toCategoryID);
            }

            st.setInt(2, questionID);

            st.executeUpdate();
        } catch (SQLException e) {
            q = null;
            st = null;
            rs = null;
            throw e;
        }
    }

    @Override
    public int getQuestionCount(Integer categoryID) throws SQLException {

        st = con.prepareStatement(sqlGetQuestionCount,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        if (categoryID == null)
            st.setNull(1, java.sql.Types.INTEGER);
        else
            st.setInt(1, categoryID);
        rs = st.executeQuery();
        rs.first();
        int ret = rs.getInt(1);
        rs.close();

        return ret;
    }

    @Override
    public void deleteQuestionFromCategory(Question toDelete, Category from)
            throws SQLException {
        try {
            st = con.prepareStatement(sqlDeleteQuestionFromCategory);
            st.setInt(1, toDelete.getQuestionID());
            st.setInt(2, from.getCategoryID());

            st.execute();
        } catch (SQLException ex) {
            st = null;
        }
    }

}
